package com.weixin.base.dao.impl;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;

import cn.org.rapid_framework.page.Page;
import cn.org.rapid_framework.page.PageRequest;

import com.weixin.base.dao.UserDao;
import com.weixin.base.util.Tools;
import com.weixin.common.BaseSpringJdbcDao;
import com.weixin.entity.BgUser;
import com.weixin.entity.TbWxUser;
import com.weixin.entity.TbWxUserUrl;

public class UserDaoImpl extends BaseSpringJdbcDao<BgUser, Integer> implements UserDao {

	static final String SELECT_PREFIX = "select nUserId,sAccount,sPassword,sName,nType,nSex,sQQ,sEmail,sMobile,sTel,sFax,sZip,sAddress,sBrief,nIsValid,dCreateTime,is_proxy,sUnPassword from tbBgUser ";

	public Class getEntityClass() {
		return BgUser.class;
	}

	public List<Map<String, Object>> getUserListbytype(int ntype) {
		String sql = "SELECT nUserId,sName AS sUserName FROM tbBgUser WHERE nType=" + ntype;
		return getSimpleJdbcTemplate().queryForList(sql);
	}

	public BgUser login(String username, String password) {

		String sql = SELECT_PREFIX + " where sAccount=? and sPassword=? and nIsValid=1";
		System.out.println(username);
		System.out.println(password);
		System.out.println(sql);
		BgUser user = null;
		try {
			user = (BgUser) getSimpleJdbcTemplate().queryForObject(sql, ParameterizedBeanPropertyRowMapper.newInstance(getEntityClass()), username, password);
		} catch (DataAccessException e) {
			e.printStackTrace();
			user = null;
		}
		return user;
	}

	public List<Map<String, Object>> getNavTreeByUser(int userID) {
		String sSQL = "SELECT DISTINCT(nNavTreeId),nPId,sPath,sName,sUrl " + "FROM tbBgNavTree WHERE nNavTreeId IN" + "(SELECT nNavTreeId FROM tbBgRightRelNavTree WHERE nRightId IN"
				+ "(SELECT nRightId FROM tbBgUserRelRight WHERE nUserId=?)) ORDER BY nNavTreeId";
		return getSimpleJdbcTemplate().queryForList(sSQL, userID);
	}

	public Page findUserByPage(PageRequest<Map> pageRequest) {
		Map map = pageRequest.getFilters();
		String sName = (String) map.get("sName");
		String nType = (String) map.get("nType");
		String sql = "";
		if (!sName.equals("")) {
			sql += " AND sName LIKE '" + sName + "%'";
		}
		if (Integer.parseInt(nType) != 0) {
			sql += " AND nType=" + nType + "";
		}
		String sql_count = "SELECT count(1) FROM tbBgUser WHERE 1=1" + sql + ";";
		String sql_page = "SELECT * FROM tbBgUser WHERE 1=1" + sql + " order by nUserId desc";
		return pageQuery(sql_page, sql_count, pageRequest, new BeanPropertyRowMapper(getEntityClass()));
	}

	public void delById(int sId) {
		String sql = "delete from tbBgUser where nUserId=?";
		getSimpleJdbcTemplate().update(sql, sId);
		sql = "DELETE FROM tbBgUserRelRight WHERE nUserId=?";
		getSimpleJdbcTemplate().update(sql, sId);
	}

	public int isExit(BgUser entity) {
		String sql = "SELECT COUNT(1) FROM tbBgUser WHERE sAccount=?";
		return getSimpleJdbcTemplate().queryForInt(sql, entity.getSaccount());
	}

	public int isExit2(BgUser entity) {
		String sql = "SELECT COUNT(1) FROM tbBgUser WHERE sAccount=? AND nUserId<>?";
		return getSimpleJdbcTemplate().queryForInt(sql, entity.getSaccount(), entity.getNuserId());
	}

	public void save(BgUser entity) {
		entity.setDcreateTime(new Date());
		String sql = "insert into tbBgUser " + " (sAccount,sPassword,sName,nType,is_proxy,nSex,sQQ,sEmail,sMobile,sTel,sFax,sZip,sAddress,sBrief,nIsValid,sUnPassword,dCreateTime) "
				+ " values " + " (:saccount,:spassword,:sname,:ntype,0,:nsex,:sqq,:semail,:smobile,:stel,:sfax,:szip,:saddress,:sbrief,:nisValid,'',:dcreateTime)";
		getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(entity));
	}

	public void saveUserRelRight(String sRoles, int nuserId) {
		String sSQL = "DELETE FROM tbBgUserRelRight WHERE nUserId=?";
		getSimpleJdbcTemplate().update(sSQL, nuserId);

		String[] sRole = sRoles.split(",");
		for (int i = 0, nLen = sRole.length; i < nLen; i++) {
			String Role = sRole[i].trim();
			if (!Role.isEmpty()) {
				String sql = "INSERT INTO tbBgUserRelRight(nUserId,nRightId) VALUES(?,?)";
				getSimpleJdbcTemplate().update(sql, new Object[] { nuserId, Integer.parseInt(Role) });
			}
		}
	}

	public void update(BgUser entity) {
		String sql = "";

		if (!entity.getSpassword().equals("")) {

			entity.setSpassword(Tools.EncoderByMd5(entity.getSpassword()));
			sql = "update tbBgUser set "
					+ " sAccount=:saccount,sName=:sname,nType=:ntype,is_proxy=0,nSex=:nsex,sQQ=:sqq,sEmail=:semail,sMobile=:smobile,sTel=:stel,sFax=:sfax,sZip=:szip,sAddress=:saddress,sBrief=:sbrief,nIsValid=:nisValid,spassword=:spassword "
					+ " where nUserId=:nuserId";
		} else {
			sql = "update tbBgUser set "
					+ " sAccount=:saccount,sName=:sname,nType=:ntype,is_proxy=0,nSex=:nsex,sQQ=:sqq,sEmail=:semail,sMobile=:smobile,sTel=:stel,sFax=:sfax,sZip=:szip,sAddress=:saddress,sBrief=:sbrief,nIsValid=:nisValid "
					+ " where nUserId=:nuserId";
		}

		getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(entity));

	}

	public Map<String, Object> selectUserRelRight(int nuserId) {
		Map<String, Object> map = new HashMap<String, Object>();
		String sql = "SELECT nRightId,sName FROM tbBgRight WHERE nIsValid=1 ORDER BY nRightId";
		List<Map<String, Object>> nRightAll = getSimpleJdbcTemplate().queryForList(sql);
		String qsql = "SELECT nUserRelRightId,nRightId,nUserId FROM tbBgUserRelRight  WHERE nUserId=?";
		List<Map<String, Object>> nUserRight = getSimpleJdbcTemplate().queryForList(qsql, nuserId);
		map.put("nRightAll", nRightAll);
		map.put("nUserRight", nUserRight);
		return map;
	}

	public BgUser getByLogin(String userName, String passWord) {
		String sql = SELECT_PREFIX + " where sAccount=? and sPassword=?";

		BgUser user = null;
		try {
			user = (BgUser) getSimpleJdbcTemplate().queryForObject(sql, ParameterizedBeanPropertyRowMapper.newInstance(getEntityClass()), userName, passWord);
		} catch (Exception e) {
			user = null;
		}
		return user;
	}

	public int updatePwd(String sAccount, String pwdmd5) {
		String sql = "UPDATE tbBgUser SET sPassword=? WHERE sAccount=?";
		return getSimpleJdbcTemplate().update(sql, pwdmd5, sAccount);
	}

	public int selectIsWrite(int oUserId, String sPageName) {
		int nRW = 1;
		String sql = "SELECT MAX(nRW) FROM tbBgRightRelNavTree a,tbBgNavTree b,tbBgUserRelRight c WHERE c.nUserId=" + oUserId + " AND b.sUrl LIKE '%" + sPageName
				+ "%' AND a.nRightId=c.nRightId AND a.nNavTreeId=b.nNavTreeId";
		try {
			nRW = getSimpleJdbcTemplate().queryForInt(sql);
		} catch (DataAccessException e) {
			nRW = 1;
			e.printStackTrace();
		}
		return nRW;
	}

	public int monitorserversql() {
		int nRW = 0;
		String sql = "SELECT top 1 nNavTreeId FROM tbbgnavtree ";
		try {
			nRW = getSimpleJdbcTemplate().queryForInt(sql);
		} catch (DataAccessException e) {
			nRW = 0;
			e.printStackTrace();
		}
		return nRW;
	}

	/**
	 * 查询微信号信息
	 */
	public TbWxUser selectWxUser(int nuserId) {
		String sql = "SELECT top 1 nWxUserId,nUserId,sName,sWxAccount,sDeveloperURL,sToken,sAppID,sAppSecret,ntype,sRemark FROM tbwxuser where nUserId=" + nuserId + " and nIsValid=1";
		TbWxUser tbWxUser = null;
		try {
			tbWxUser = (TbWxUser) getSimpleJdbcTemplate().queryForObject(sql, ParameterizedBeanPropertyRowMapper.newInstance(TbWxUser.class));
		} catch (DataAccessException e) {
			tbWxUser = new TbWxUser();
			tbWxUser.setNtype(1);
			tbWxUser.setNWxUserId(0);
			tbWxUser.setNUserId(nuserId);
			tbWxUser.setSAppID("");
			tbWxUser.setSAppSecret("");
			tbWxUser.setSDeveloperURL("");
			tbWxUser.setSName("");
			tbWxUser.setSRemark("");
			tbWxUser.setSToken("");
			tbWxUser.setSWxAccount("");
		}
		return tbWxUser;
	}

	/**
	 * 添加微信号信息
	 */
	public int addWxUser(TbWxUser tbWxUser) {
		tbWxUser.setDCreateTime(new Date());
		tbWxUser.setNIsValid(1);

		String sql = "INSERT INTO tbwxuser (nUserId,sName,sWxAccount,sDeveloperURL,sToken,sAppID,sAppSecret,ntype,nIsValid,dCreateTime,sRemark,nTemplateId) values "
				+ " (:nUserId,:sName,:sWxAccount,:sDeveloperURL,:sToken,:sAppID,:sAppSecret,:ntype,:nIsValid,:dCreateTime,:sRemark,:nTemplateId)";
		return getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(tbWxUser));
	}

	/**
	 * 添加用户首页链接地址表
	 */
	public int addWxUserUrl(TbWxUserUrl tbWxUserUrl) {

		String sql = "INSERT INTO tbwxuserurl (nUserId,sName,sUrl,nType,nIsValid,dCreateTime) values (:nUserId,:sName,:sUrl,:nType,:nIsValid,:dCreateTime)";
		return getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(tbWxUserUrl));
	}

	/**
	 * 修改微信号信息
	 */
	public int upWxUser(TbWxUser tbWxUser) {
		String sql = "update tbwxuser set " + " sName = :sName,sWxAccount = :sWxAccount,sDeveloperURL = :sDeveloperURL,sToken = :sToken,"
				+ " sAppID = :sAppID,sAppSecret = :sAppSecret,ntype = :ntype,sRemark = :sRemark " + " WHERE nWxUserId=:nWxUserId and nUserId=:nUserId ";

		return getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(tbWxUser));
	}
}
